<?php
/* for processing one to many relationships like multiple images or documents related to a single project
 * we should utilize models here
 * 1. one_model, many_model (projects_model, images_model)
 * 2. relational fields (one_model->getIdField() => relational field, many_model->getIdField() => relational field)
 */

/*
 * TODO: eventually this should probably extend a table model somehow and be equipped to deal with multiple OneToMany fields?
 */

class OneToMany_model extends Model{
	var $many_model = null;
	
	//var $one_table = null;
	var $many_table = null;
	//var $one_id_field = null;
	var $many_id_field = null;
	
	var $table = null;
	var $relational_fields = null;
	var $select_field = null;
	var $option_field = null;
	var $order_field = 'order_num';
	
	function OneToMany_model(){
		parent::Model();
		$this->load->database();
		$ci =& get_instance();
		$this->db =& $ci->db;
		if( $this->many_model ){
			$this->setManyModelData();
		}
	}
	
	function setManyModelData(){
		$this->many_table = $this->many_model->getTable();
		$this->many_id_field = $this->many_model->getIdField();
	}
	
	function setManyModel( $model ){
		if( $model ){
			$this->many_model = $model;
			$this->setManyModelData();
		}
	}
	
	/*function setOneTable( $table ){
		if( $this->db->table_exists($table)){
			$this->one_table = $table;
		}
	}
	
	function setOneIdField( $field ){
		if( $this->one_table && $this->db->field_exists($field, $this->one_table)){
			$this->one_id_field = $field;
		}
	}*/
	
	/* sets the relational table */
	function setTable($table){
		if( $this->db->table_exists($table)){
			$this->table = $table;
		}else{
			trigger_error("OneToMany_model::setTable - table $table does not exist!", E_USER_ERROR);
		}
	}
	
	function setOptionField( $field ){
		$this->option_field = $field;
	}
	
	function getOptionField(){
		return $this->option_field;
	}
	
	/* sets and gets the field in the relational table which determines whether
	 * an entry is selected (i.e. whether it should be used)
	 * 
	 * this is optional
	 */
	function setSelectField($field){
		$this->select_field = $field;
	}
	function getSelectField($field){
		return $this->select_field;
	}
	
	/* sets and gets the field by which the items from the many table should
	 * be ordered.
	 */
	function setOrderField($field){
		$this->order_field = $field;
	}
	function getOrderField($field){
		return $this->order_field;
	}
	
	/* sets the fields used to index the relational table, these fields will
	 * most often correspond to the primary key fields of the one table and 
	 * the many table.
	 */
	function setRelationalFields($one_field, $many_field){
		$fields = array($one_field, $many_field);
		if( $this->relational_fields ){
			unset($this->relational_fields);
		}
		$this->relational_fields = $fields;
	}
	
	function _tablesExist($tables){
		if( !is_array($tables) ){
			return $this->db->table_exists($tables);
		}else{
			foreach($tables as $table){
				if( !$this->db->table_exists($table)){
					return false;
				}
			}
			return true;
		}
	}
	
	function _onlyTableFieldData( &$data, $table){
		$d = array();
		if( $this->_tablesExist($table)){
			$fields = $this->db->list_fields($table);
			foreach($fields as $field){
				if( isset($data[$field]) ){
					$d[$field] = $data[$field];
				}
			}
		}
		return $d;
	}
	
	/* saves the item into the data table and adds an entry in the relational
	 * table to tie the item with $id to the parent item with $parent_id
	 */
	function saveItem( &$upload_data, $id = null, $parent_id = null ){
		/* allow saving of the item itself and the relational entry in one step */
		if( $this->many_model){
			$ret = $this->many_model->saveItem($upload_data, $id);
			if( $parent_id ){
				$this->addRelatedItems( $parent_id, $ret);
			}
			return $ret;
		}
		return null;
	}
	
	/* adds a relational entry given the $id (parent id) and the $item_id
	 */
	function addRelatedItems( $id, $item_id){
		if( is_numeric($id) ){
			if( !is_array($item_id) ){
				$item_id = array($item_id);
			}
			$item_id = array_map( array($this->db, 'escape'), array_filter( $item_id, 'is_numeric'));
			/* want to only use ids that are in the many table so do that here */
			$id_query = $this->db->query("SELECT {$this->relational_fields[1]} FROM {$this->many_model->getTable()} WHERE {$this->relational_fields[1]} IN (" . implode(',', $item_id) . ')');
			$ids = array();
			if( $id_query && $id_query->num_rows()){
				$id_query = $id_query->result_array();
				foreach( $id_query as $row){
					$ids[] = $row[$this->relational_fields[1]];
				}
			}
			if( count($ids) ){
				$values = "( $id, " . implode( "), ($id, ", $ids) . ')';
				$this->db->query("INSERT INTO {$this->table} ({$this->relational_fields[0]}, {$this->relational_fields[1]}) VALUES $values");
			}
		}
	}
	
	/* sets the select field in the relational table to $select_value
	 */
	function selectRelatedItems( $id, $iid, $select_value = 1){
		if( $this->select_field ){
			if( is_numeric($id) ){
				if( !is_array($iid) ){
					$iid = array($iid);
				}
				$iid = array_map( array($this->db, 'escape'), array_filter( $iid, 'is_numeric'));
				if( count($iid) ){
					$select_value = $this->db->escape($select_value);
					$this->db->query("UPDATE {$this->table} SET {$this->select_field} = $select_value WHERE {$this->relational_fields[0]} = $id AND {$this->relational_fields[1]} IN (" . implode(',', $iid) . ')');
				}
			}
		}
	}
	
	/* sets the select field of all the related items
	 */
	function selectAllRelatedItems( $id, $select_value = 1){
		if( $this->select_field && is_numeric($id) ){
			$select_value = $this->db->escape($select_value);
			$this->db->query("UPDATE {$this->table} SET {$this->select_field} = $select_value WHERE {$this->relational_fields[0]} = $id");
		}
	}
	
	/* delete all relational items from the table based on the parent id; e.g. delete all related images for a project with project_id */
	function deleteRelatedItems( $id){
		$ret = array();
		
		if( is_numeric($id) && $this->table && $this->db->table_exists($this->table) && 
		    $this->db->field_exists($this->relational_fields[0], $this->table) && 
			$this->db->field_exists($this->relational_fields[1], $this->table) ){
		    $query = $this->db->query("SELECT GROUP_CONCAT( DISTINCT {$this->relational_fields[1]} SEPARATOR ',') as id_list FROM {$this->table} WHERE {$this->relational_fields[0]} = $id");
			if( $query && $query->num_rows() ){
				$temp = $query->row_array();
				if( !empty($temp['id_list'])){
					$ret = explode(',', $temp['id_list']);
				}
			}
			$this->db->where(array($this->relational_fields[0] => $id));
			$this->db->delete($this->table);
		}
		
		return $ret;
	}
	
	/* delete an entry from the related table based on it's item id; e.g. delete a related image entry where the image_id is in $id */
	function deleteRelatedItemsById($id){
		if(!is_array($id)){
			$id = array($id);
		}
		$id = array_filter($id, 'is_numeric');
		if( count($id) && $this->table && $this->db->table_exists($this->table) && 
		    $this->db->field_exists($this->relational_fields[1], $this->table)){
			$this->db->query("DELETE FROM {$this->table} WHERE {$this->relational_fields[1]} IN (" . implode(',', $id) . ')');
		}
	}
	
	function updateSelectedRelatedItems( $id, $iid){
		$this->selectAllRelatedItems( $id, 0);
		$this->selectRelatedItems( $id, $iid);
	}
	
	function saveSelectedRelatedItemsOrder( $id, &$sort){
		if( $this->db->field_exists($this->order_field, $this->table) && is_array($sort)){
			$case = "CASE";
			$i = 0;
			foreach($sort as $sort_id){
				$case .= " WHEN {$this->relational_fields[1]} = " . $this->db->escape($sort_id) . " THEN $i";
				$i++;
			}
			$case .= " ELSE {$this->order_field} END";
			$sql = "UPDATE {$this->table} SET {$this->order_field} = $case WHERE {$this->relational_fields[0]} = " . $this->db->escape($id);
			return $this->db->query($sql);
		}
	}
	
	function _checkTableAndFields($table, &$fields){
		if( $this->db->table_exists($table)){
			foreach($fields as $field){
				if(!$this->db->field_exists($field, $table)){
					return false;
				}
			}
			return true;
		}else{
			return false;
		}
	}
	
	/* need to modify this to return an array containing id and value */
	function getSingleItemOption( $id ){
		if( $this->many_table && $this->option_field && 
		    $this->_checkTableAndFields( $this->table, $this->relational_fields) &&
			$this->db->field_exists($this->option_field, $this->many_table) ){
			
			//$select = $this->many_table . '.' . $this->option_field;
			$select = $this->many_table . '.' . $this->many_id_field . ', ' . $this->many_table . '.' . $this->option_field;
			$this->db->select($select);
			$this->db->from($this->table);
			$this->db->join($this->many_table, $this->table . '.' . $this->relational_fields[1] . '=' . $this->many_table . '.' . $this->many_id_field, 'LEFT');	
			$this->db->where( array( $this->table . '.' . $this->relational_fields[0] => $id));
			$query = $this->db->get();
			if( $query && $query->num_rows()){
				$query = $query->result_array();
				//return $query[0][$this->option_field];
				return array($query[0][$this->many_id_field], $query[0][$this->option_field]);
			}
		}
		return null;
	}
	
	function getItemOptions( $id){
		$result = array();
		if( $this->many_table && $this->option_field && 
		    $this->_checkTableAndFields( $this->table, $this->relational_fields) &&
			$this->db->field_exists($this->option_field, $this->many_table) ){
			$select = $this->many_table . '.' . $this->many_id_field . ', ' . $this->many_table . '.' . $this->option_field;
			$this->db->select($select);
			$this->db->from($this->table);
			$this->db->join($this->many_table, $this->table . '.' . $this->relational_fields[1] . '=' . $this->many_table . '.' . $this->many_id_field, 'LEFT');
			$this->db->where( array( $this->table . '.' . $this->relational_fields[0] => $id));
			/*if( $this->db->field_exists('order_num', $this->table)){
				$this->db->order_by( $this->table . '.order_num', 'asc');
			}*/
			if( $this->order_field && $this->db->field_exists($this->order_field, $this->table)){
				$this->db->order_by( $this->table . '.' . $this->order_field, 'asc');
			}
			$query = $this->db->get();
			foreach( $query->result() as $row){
				$result[$row->{$this->many_id_field}] = $row->{$this->option_field};
			}
		}
		return $result;
	}
	
	function getSelectedItems( $id){
		if( is_numeric($id) && $this->select_field && $this->_checkTableAndFields($this->table, array_merge($this->relational_fields, array($this->select_field)))){
			$this->db->select($this->relational_fields[1]);
			$this->db->where($this->relational_fields[0], $id);
			$this->db->where($this->select_field, 1);
			$result = array();
			$query = $this->db->get($this->table);
			if( $query && $query->num_rows()){
				foreach($query->result() as $row){
					$result[] = $row->{$this->relational_fields[1]};
				}
			}
			return $result;
		}
		return array();
	}
	
	function deleteItems( $ids, $delete_relational = false ){
		if( $this->many_model){
			/* delete the items from the many model instructing it NOT to try and delete entries
			 * from the relational tables as this has already been done
			 */
			$this->many_model->deleteItems($ids, $delete_relational);
		}
	}
	
	function getItemsData( $id, $limit = 0, $orderby = null, $selected = 1){
		$result = array();
		if( is_numeric($id) && $this->many_table && $this->_checkTableAndFields( $this->table, $this->relational_fields)){
			$this->db->from($this->table);
			$this->db->join($this->many_table, $this->table . '.' . $this->relational_fields[1] . '=' . $this->many_table . '.' . $this->many_id_field, 'LEFT');
			$this->db->where( array( $this->table . '.' . $this->relational_fields[0] => $id));
			if( $orderby ){
				if( is_array($order_by)){
					$this->db->order_by($orderby[0], $orderby[1]);
				}else{
					$this->db->order_by($orderby);
				}
			}
			if( $limit ){
				if( is_array($limit)){
					if( is_numeric($limit[0]) && is_numeric($limit[1])){
						$this->db->limit($limit[0], $limit[1]);
					}
				}else if( is_numeric($limit)){
					$this->db->limit($limit);
				}
			}
			$query = $this->db->get();
			foreach( $query->result_array() as $row){
				$result[$row[$this->many_id_field]] = $row;
			}
		}
		return $result;
	}
}

?>